﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;
using System.Data.SqlClient;


/// <summary>
/// Summary description for AdminBussinessObject
/// </summary>
public class AdminBussinessObject
{
    static SqlConnection cn = new SqlConnection("User Id=tyros;Password=Gits123%;Database= SkilledRESKAT;Data Source= 182.50.141.238");//("User Id= sa; Password= 123; Database= SkilledRESKAT; Data Source=.");
    static SqlCommand cmd;

	public AdminBussinessObject()
	{

	}

    public static DataTable DisplayAvailableGroups( )
    {        
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@TransID", 0);
        DataTable DisplayAvailableGroups = new DataTable();
        da.Fill(DisplayAvailableGroups);
        return DisplayAvailableGroups;
    }

    public static DataTable DisplayCount( int GroupID)
    {
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@TransID", 5);
        cmd.Parameters.AddWithValue("@GroupID", GroupID);
        DataTable DisplayCount = new DataTable();
        da.Fill(DisplayCount);
        return DisplayCount;


    }

    public static DataTable DisplayParticularGroup( int GroupID)
    {
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_GetSetGroupsMaster";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        cmd.Parameters.AddWithValue("@TransID", 1);
        cmd.Parameters.AddWithValue("@GroupID",GroupID);
        DataTable DisplayParticularGroup = new DataTable();
        da.Fill(DisplayParticularGroup);
        return DisplayParticularGroup;
    }


    public static void InsertNewGroup(string GroupName, string Desc, int IsActive)
    {
        //cmd = new SqlCommand("sp_AddNewGroup", cn);
        cmd = new SqlCommand("sp_GetSetGroupsMaster", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@GroupName", GroupName);        
        cmd.Parameters.AddWithValue("@Desc", Desc);
        cmd.Parameters.AddWithValue("@IsActive", IsActive);
        cmd.Parameters.AddWithValue("@TransID", 3);
        if (cn.State==ConnectionState.Closed)
        {
            cn.Open();
        }
        cmd.ExecuteNonQuery();
    }

    public static void UpdateExistingGroup( int GroupID,string GroupName, string Desc, int IsActive)
    {
        //cmd = new SqlCommand("sp_AddNewGroup", cn);
        cmd = new SqlCommand("sp_GetSetGroupsMaster", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransID",3);
        cmd.Parameters.AddWithValue("@GroupID", GroupID);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);
        cmd.Parameters.AddWithValue("@Desc", Desc);
        cmd.Parameters.AddWithValue("@IsActive", IsActive);

        if (cn.State== ConnectionState.Closed)
        {
            cn.Open();
        }        
        cmd.ExecuteNonQuery();
    }



    public static DataTable DisplayAllUsers()
    {
        cmd = new SqlCommand();
        cmd.Connection = cn;
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "sp_FetchAllUsers";
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable DisplayAllUsers = new DataTable();
        DisplayAllUsers.Clear();
        da.Fill(DisplayAllUsers);
        return DisplayAllUsers;
    }

    public static DataTable InsertNewUser(string FirstName, string LastName, string LoginID, string Password, string PhoneNumber, string Email, string Address, int PromptforPwdChange, int IsUserActive)
    {
        DataTable dt = new DataTable();
        cmd = new SqlCommand("sp_AddNewUser", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransID",1);
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@LoginID", LoginID);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
        cmd.Parameters.AddWithValue("@EmailID", Email);
        cmd.Parameters.AddWithValue("@Address", Address);
        cmd.Parameters.AddWithValue("@PromptforPasswordChange", PromptforPwdChange);
        cmd.Parameters.AddWithValue("@IsUserActive", IsUserActive);
        if (cn.State== ConnectionState.Closed)
        {
            cn.Open();
        }        
        //cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        return dt;       
    }


    public static void UpdateExistingUser(int UserID,string FirstName, string LastName, string LoginID, string Password, string PhoneNumber, string Email, string Address, int PromptforPwdChange, int IsUserActive)
    {
        
        cmd = new SqlCommand("sp_AddNewUser", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@TransID", 2);
        cmd.Parameters.AddWithValue("@UserID",UserID);
        cmd.Parameters.AddWithValue("@FirstName", FirstName);
        cmd.Parameters.AddWithValue("@LastName", LastName);
        cmd.Parameters.AddWithValue("@LoginID", LoginID);
        cmd.Parameters.AddWithValue("@Password", Password);
        cmd.Parameters.AddWithValue("@PhoneNumber", PhoneNumber);
        cmd.Parameters.AddWithValue("@EmailID", Email);
        cmd.Parameters.AddWithValue("@Address", Address);
        cmd.Parameters.AddWithValue("@PromptforPasswordChange", PromptforPwdChange);
        cmd.Parameters.AddWithValue("@IsUserActive", IsUserActive);
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open(); 
        }        
        cmd.ExecuteNonQuery();
    }



    public static DataTable GetUserID(string LoginID)
    {
        DataTable dt = new DataTable();
        cmd = new SqlCommand("sp_GetUserID", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@LoginID", LoginID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        da.Fill(dt);
        return dt;
    }

    public static void InsertintoUserGroups(int UserID, string GroupName)
    {

        cmd = new SqlCommand("sp_InsertIntoUserGroups", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserID", UserID);
        cmd.Parameters.AddWithValue("@GroupName", GroupName);        
        if (cn.State == ConnectionState.Closed)
        {
            cn.Open();
        }
        cmd.ExecuteNonQuery();
    }




    public static DataTable GetAllUsersorParticularUser(int Gid, string StudName)
    {
        cmd = new SqlCommand("sp_GetAllUsersorParticularUser", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@GroupID", Gid);
        cmd.Parameters.AddWithValue("@StudentName", StudName);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable();
        da.Fill(dt);       
        return dt;
    }


    public static DataTable GetUserInfo(int UserID)
    {
        cmd = new SqlCommand("sp_GetUserInfo", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserID", UserID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable GetUserInfoDT = new DataTable();
        da.Fill(GetUserInfoDT);
        return GetUserInfoDT;
    }

    public static DataTable GetUsersGroup(int UserID)
    {
        cmd = new SqlCommand("sp_GetUsersGroup", cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@UserID", UserID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable GetUsersGroupDT = new DataTable();
        da.Fill(GetUsersGroupDT);
        return GetUsersGroupDT;
    }

    public static DataTable GetUserByLoginID(string LoginID)
    {
        cmd = new SqlCommand("sp_GetUserByLoginID",cn);
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@LoginID", LoginID);
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataTable dt = new DataTable(); 
        da.Fill(dt);
        return dt;
    }



}